//201902104011王雪梦
package bysj.dao;
import bysj.domain.Department;
import bysj.domain.School;
import bysj.service.SchoolService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class DepartmentDao {
    private static DepartmentDao departmentDao = null;

    //将构造器私有，防止本类外的类生成本类的对象
    private DepartmentDao() {
    }

    //返回本类唯一的对象
    public static DepartmentDao getInstance() {
        // 静态变量departmentDao为空时，才创建DepartmentDao对象。
        // 第二次调用本方法departmentDao就不空了，不再创建对象。
        if (departmentDao == null) {
            departmentDao = new DepartmentDao();
        }
        return departmentDao;
    }

    public int countAll(School school) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String department_sql = "select count(id) as cnt_by_school_id from department where school_id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(department_sql);
        preparedStatement.setInt(1, school.getId());
        int count = 0;
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            count = resultSet.getInt("cnt_by_school_id");
        }
        JdbcHelper.close(preparedStatement, connection);
        return count;
    }

    public Collection<Department> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        Collection<Department> departments = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM department");
        int totalNum = CommonDao.getInstance().count(connection,"department");
        System.out.println(totalNum);
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            //System.out.println(sql_select);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            //获得result的参数值
            int id = resultSet.getInt(1);
            String description = resultSet.getString(2);
            String no = resultSet.getString(3);
            String remarks = resultSet.getString(4);
            int schoolId = resultSet.getInt(5);
            School school = SchoolService.getInstance().find(schoolId);
            //为department赋值
            Department department = new Department(id, description, no, remarks, school);
            //将department添加到departments集合里
            departments.add(department);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement, connection);
        //返回departments集合
        return departments;
    }

    public Department find(Integer id,Connection connection) throws SQLException {
        Department department = null;
        //创建sql语句
        String department_sql = "SELECT * FROM department WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt = connection.prepareStatement(department_sql);
        //为预编译对象赋值
        pstmt.setInt(1, id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            //获得result的参数值
            String description = resultSet.getString(2);
            String no = resultSet.getString(3);
            String remarks = resultSet.getString(4);
            int schoolId = resultSet.getInt(1);
            School school = SchoolDao.getInstance().find(5,connection);
            //为department赋值
            department = new Department(schoolId, description, no, remarks, school);
            System.out.println(department);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return department;
    }

    public boolean update(Department department,Connection connection) throws SQLException {
        //准备预编译语句
        String department_sql = "update department set description=?,no=?,remarks=? ,school_id=? where id="
                + department.getId();
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(department_sql);
        //为预编译对象赋值
        preparedStatement.setString(1, department.getDescription());
        preparedStatement.setString(2, department.getNo());
        preparedStatement.setString(3, department.getRemarks());
        preparedStatement.setInt(4, department.getSchool().getId());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }

    public boolean add(Department department,Connection connection) throws SQLException {
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement("Insert into department(description,no,remarks,school_id) Values " + "(?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
        //为预编译对象赋值
        preparedStatement.setString(1, department.getDescription());
        preparedStatement.setString(2, department.getNo());
        preparedStatement.setString(3, department.getRemarks());
        preparedStatement.setInt(4, department.getSchool().getId());
        //执行预编译对象的executeUpdate方法，获得增加的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String department_sql = "delete from department where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(department_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(pstmt);
        return affectedRowNum > 0;
    }
}
